import operator
from osv import osv, fields
from osv.orm import intersect
import tools.sql
from tools.translate import _

class base_kdvn_functions_postgres(osv.osv):
        _name = "base.kdvn.functions.postgres"
        def init(self, cr):
                cr.execute("select lanname from pg_language where lanname='plpgsql';")
                res=cr.fetchall()
                if res is None:
                    cr.execute("CREATE PROCEDURAL LANGUAGE plpgsql;")
                #Convert Pricelist to currency name
                cr.execute("CREATE OR REPLACE FUNCTION fncurrpltocname(pid integer) RETURNS text AS \
                        $BODY$ select code from res_currency where id=(select currency_id from \
                        product_pricelist where id = $1) $BODY$ \
                        LANGUAGE 'sql' VOLATILE COST 100;")

                #Exchange currency from Currecny, To Currency, Date
#                cr.execute("CREATE OR REPLACE FUNCTION fncompute(fromcurr integer, tocurr integer, datecurr timestamp) RETURNS numeric AS \
#                        $BODY$ Select rate/(Select rate from res_currency_rate where currency_id=$1 and name=(Select max(name) as name \
#                        from res_currency_rate where currency_id=$1 and name<=$3)) from res_currency_rate where currency_id=$2 and name=(Select max(name) as name \
#                        from res_currency_rate where currency_id=$2 and name<=$3) $BODY$ LANGUAGE 'sql' VOLATILE COST 100;")
                
                
                cr.execute("CREATE OR REPLACE FUNCTION fncompute(fromcurr integer, tocurr integer, datecurr date)\
                                RETURNS numeric AS\
                                $BODY$\
                                Select  case when $1=$2 then 1 else (Select rate/(Select rate from res_currency_rate where currency_id=$1 and name=(Select max(name) as name\
                                from res_currency_rate where currency_id=$1 and name<=$3)) from res_currency_rate where ($1=$2) or (currency_id=$2 and name=(Select max(name) as name\
                                from res_currency_rate where currency_id=$2 and name<=$3))) end;\
                                $BODY$\
                                LANGUAGE 'sql' VOLATILE\
                                COST 100;")
                
                cr.execute("CREATE OR REPLACE FUNCTION fncompute(fromcurr integer, tocurr integer, datecurr timestamp without time zone)\
                              RETURNS numeric AS\
                              $BODY$\
                            Select case when $1=$2 then 1 else (Select rate/(Select rate from res_currency_rate where currency_id=$1 and name=(Select max(name) as name\
                            from res_currency_rate where currency_id=$1 and name<=$3)) from res_currency_rate where ($1=$2) or (currency_id=$2 and name=(Select max(name) as name\
                            from res_currency_rate where currency_id=$2 and name<=$3))) end;\
                            $BODY$\
                          LANGUAGE 'sql' VOLATILE\
                          COST 100;")
                cr.execute("CREATE OR REPLACE FUNCTION fncompute(fromcurr integer, tocurr integer, datecurr timestamp)\
                              RETURNS numeric AS\
                              $BODY$\
                              Select case when $1=$2 then 1 else (Select rate/(Select rate from res_currency_rate where currency_id=$1 and name=(Select max(name) as name\
                              from res_currency_rate where currency_id=$1 and name<=$3)) from res_currency_rate where ($1=$2) or (currency_id=$2 and name=(Select max(name) as name\
                              from res_currency_rate where currency_id=$2 and name<=$3))) end;\
                              $BODY$\
                              LANGUAGE 'sql' VOLATILE\
                              COST 100;")
                #Convert currency id to currency name
                cr.execute("CREATE OR REPLACE FUNCTION fncurridtoname(currid integer) RETURNS text AS 'select code from res_currency where id=$1' \
                        LANGUAGE 'sql' VOLATILE COST 100;")

                #Convert currency name to currency id
                cr.execute("CREATE OR REPLACE FUNCTION fncurrnametoid(pcurrname text) RETURNS integer AS \
                        'Select id from res_currency where code=$1' LANGUAGE 'sql' VOLATILE COST 100;")

                #convert pricelist to currency id
                cr.execute("CREATE OR REPLACE FUNCTION fncurrpltocid(pid integer) RETURNS integer AS\
                        'select currency_id from product_pricelist where id = $1' LANGUAGE 'sql' VOLATILE COST 100;")
                #View on Account Analytic Summary
             #   tools.sql.drop_view_if_exists(cr, 'account_analytic_summary_budget_balance_detail')
             #   tools.sql.drop_view_if_exists(cr, 'account_analytic_summary_project_budget_detail')


#                tools.sql.drop_view_if_exists(cr,'drop function fnbudgetwithoutcontract(pid integer, panalyticcode character, pbudgetcode character)')
 #               tools.sql.drop_view_if_exists(cr,'drop function fnbudgetmade(pid integer, panalyticcode character, pbudgetcode character)')
  #              tools.sql.drop_view_if_exists(cr, 'vwpo')
   #             tools.sql.drop_view_if_exists(cr, 'vwanalytic_budget')
    #            tools.sql.drop_view_if_exists(cr, 'vwanalytic_info')
     #           tools.sql.drop_view_if_exists(cr, 'vwproduct_to_budget')
      #          tools.sql.drop_view_if_exists(cr, 'vwproduct_account')
       #         tools.sql.drop_view_if_exists(cr, 'vwproduct_template_account_rel')
                
#               
#                cr.execute("CREATE OR REPLACE VIEW vwproduct_template_account_rel AS SELECT DISTINCT\
#                            substr(ir_property.res_id::text, 18, length(ir_property.res_id::text) - 17) AS product_tmpl_id,\
#                            substr(ir_property.value::text, 17, length(ir_property.value::text) - 16) AS account_id\
#                            FROM ir_property WHERE ir_property.res_id::text ~~ 'product_template%'::text AND\
#                            (ir_property.name::text = ANY (ARRAY['property_account_income'::character varying, 'property_account_expense'::character varying]::text[]))\
#                            ORDER BY substr(ir_property.res_id::text, 18, length(ir_property.res_id::text) - 17), \
#                            substr(ir_property.value::text, 17, length(ir_property.value::text) - 16);")
#
#                cr.execute("CREATE OR REPLACE VIEW vwproduct_account AS SELECT pp.id, to_number(vwpa.account_id, '999999'::text) AS account_id\
#                            FROM product_product pp LEFT JOIN vwproduct_template_account_rel vwpa \
#                            ON pp.product_tmpl_id::numeric = to_number(vwpa.product_tmpl_id, '9999999999'::text);")
#
#                cr.execute("CREATE OR REPLACE VIEW vwproduct_to_budget AS\
#                            SELECT vpa.id, abp.name, abp.code, ab.budget_id FROM vwproduct_account vpa\
#                            LEFT JOIN account_budget_rel ab ON vpa.account_id = ab.account_id::numeric\
#                            LEFT JOIN account_budget_post abp ON ab.budget_id = abp.id;")
#
#                cr.execute("CREATE OR REPLACE VIEW vwanalytic_info AS SELECT aaa.id, so.project_id, aaa.code, aaa.name,\
#                            so.amount_untaxed AS jobamount, fncurrpltocname(so.pricelist_id) AS jobcurr,\
#                            fncompute(fncurrpltocid(so.pricelist_id), fncurrnametoid('VND'), aaa.date_start) AS jobcurramount,aaa.date_start AS date_curr,\
#                            so.amount_untaxed * fncompute(fncurrpltocid(so.pricelist_id), fncurrnametoid('VND'::text), aaa.date_start)::double precision AS jobamountvnd\
#                            FROM account_analytic_account aaa LEFT JOIN sale_order so ON so.project_id = aaa.id\
#                            LEFT JOIN product_pricelist ppl ON so.pricelist_id = ppl.id LEFT JOIN res_currency rc \
#                            ON ppl.currency_id = rc.id;")
#
#                cr.execute("CREATE OR REPLACE VIEW vwanalytic_budget AS  SELECT aaa.id AS analytic_id, cbl.general_budget_id,\
#                            cbl.planned_amount AS budgetamount, vptb.id AS product_id,\
#                            fncompute(fncurrnametoid('USD'::text), fncurrnametoid('VND'::text), aaa.date_start) * cbl.planned_amount AS budgetamount_vnd\
#                            FROM account_analytic_account aaa LEFT JOIN crossovered_budget_lines cbl ON\
#                            aaa.id = cbl.analytic_account_id LEFT JOIN vwproduct_to_budget vptb ON\
#                            cbl.general_budget_id = vptb.budget_id;")
#
#                cr.execute("CREATE OR REPLACE VIEW vwpo AS SELECT po.id, po.origin AS contract_number, po.date_order AS date,\
#                            rp.name AS supplier,vwptb.code AS budgetcode, vwptb.name AS budgetname, vwai.code,\
#                            vwai.name AS project_name, vwai.jobamount, vwai.jobcurr, vwai.jobcurramount,\
#                            vwai.jobamountvnd AS jobamount_vnd, fncurrpltocname(po.pricelist_id) AS pocurr,\
#                            po.amount_untaxed AS poamount,\
#                            fncompute(fncurrpltocid(po.pricelist_id), fncurrnametoid('VND'::text),\
#                            vwai.date_curr)::double precision * po.amount_untaxed AS poamount_vnd,vab.budgetamount_vnd\
#                            FROM purchase_order po LEFT JOIN purchase_order_line pol ON po.id = pol.order_id\
#                            LEFT JOIN product_product pr ON pol.product_id = pr.id\
#                            LEFT JOIN product_template pt ON pr.product_tmpl_id = pt.id\
#                            LEFT JOIN res_partner rp ON po.partner_id = rp.id\
#                            LEFT JOIN vwproduct_to_budget vwptb ON pol.product_id = vwptb.id\
#                            LEFT JOIN vwanalytic_info vwai ON pol.account_analytic_id = vwai.id\
#                            LEFT JOIN vwanalytic_budget vab ON pol.account_analytic_id = vab.analytic_id\
#                            AND pol.product_id = vab.product_id;")
#
#                cr.execute("CREATE OR REPLACE FUNCTION fnbudgetmade(pid integer, panalyticcode character, pbudgetcode character)\
#                            RETURNS double precision AS $BODY$ select sum(poamount_vnd) from vwpo where\
#                            contract_number<>'' and id<$1 and code=$2 and budgetcode=$3 group by budgetcode$BODY$\
#                            LANGUAGE 'sql' VOLATILE COST 100;")
#
#                cr.execute("CREATE OR REPLACE FUNCTION fnbudgetwithoutcontract(pid integer, panalyticcode character, pbudgetcode character)\
#                            RETURNS double precision AS 'select sum(poamount_vnd) from vwpo\
#                            where contract_number isnull and id<$1 and code=$2 and budgetcode=$3 group by budgetcode'\
#                            LANGUAGE 'sql' VOLATILE COST 100;")
                
#                cr.execute("CREATE OR REPLACE FUNCTION fnjobexrate(jobid integer, frcurr integer, tocurr integer)\
#                              RETURNS numeric AS\
#                                'Select (Select rate from res_currency_rate where currency_id=$3 and\
#                                  name=(Select max(name) from res_currency_rate where currency_id=$3 and\
#                                  name<=(Select dateofregistration from (Select pid,dateofregistration from (Select pid,id from (Select parent_id as pid,min(code) as pcode from account_analytic_account where type=''normal'' group by parent_id) vw\
#                                    left join account_analytic_account on code=pcode) vwtemp\
#                                left join account_analytic_account aaa on vwtemp.id=aaa.id) vwtmp where pid=$1)))/\
#                                (Select rate from res_currency_rate where currency_id=$2 and\
#                                name=(Select max(name) from res_currency_rate where currency_id=$2 and\
#                                name<=(Select dateofregistration from (Select pid,dateofregistration from (Select pid,id from (Select parent_id as pid,min(code) as pcode from account_analytic_account where type=''normal'' group by parent_id) vw\
#                                        left join account_analytic_account on code=pcode) vwtemp\
#                                left join account_analytic_account aaa on vwtemp.id=aaa.id) vwtmp\
#                                where pid=$1)))'\
#                            LANGUAGE 'sql' VOLATILE COST 100;")                
base_kdvn_functions_postgres()
